#include "excelengine.h"
#include <QMessageBox>
#include <QFile>
#include <QFileInfo>
#include <QDir>
#include <QDebug>

ExcelEngine::ExcelEngine(QObject *parent)
    : QObject(parent)
{
     sXlsFile     = "";
     nRowCount    = 0;
     nColumnCount = 0;
     nStartRow    = 0;
     nStartColumn = 0;
     bIsOpen     = false;
     bIsValid    = false;
     bIsANewFile = false;
     bIsSaveAlready = false;
}

ExcelEngine::ExcelEngine(QString xlsFile, QObject *parent)
    : QObject(parent)
{
     sXlsFile     = xlsFile;
     nRowCount    = 0;
     nColumnCount = 0;
     nStartRow    = 0;
     nStartColumn = 0;
     bIsOpen     = false;
     bIsValid    = false;
     bIsANewFile = false;
     bIsSaveAlready = false;

}

ExcelEngine::~ExcelEngine()
{
     if (bIsOpen)
     {
          Close();
     }
     if (pWorksheet)
          delete pWorksheet;
     if (pExcel)
          delete pExcel;
}
/**
  *@brief 打开sXlsFile指定的excel报表
  *@return true : 打开成功
  *        false: 打开失败
  */
bool ExcelEngine::Open(uint nSheet, bool visible)
{
     if(bIsOpen)
     {
          Close();
     }
     nCurrSheet = nSheet;
     if(NULL == pExcel)
     {
          pExcel = new QXlsx::Document(sXlsFile);
          if(pExcel)
          {
               bIsValid = true;
          }
          else
          {
               bIsValid = false;
               bIsOpen  = false;
               return bIsOpen;
          }
     }
     if(!bIsValid)
     {
          bIsOpen = false;
          return bIsOpen;
     }

     if(sXlsFile.isEmpty())
     {
          bIsOpen = false;
          return bIsOpen;
     }
     /*如果指向的文件不存在，则需要新建一个*/
     QFile f(sXlsFile);
     if(!f.exists())
     {
          bIsANewFile = true;
     } else
     {
          bIsANewFile = false;
     }
     if(!bIsANewFile)
     {

     }
     else
     {
          pExcel->addSheet(); //新建一个xls
     }
     QStringList sheetNames = pExcel->sheetNames();
     if (nCurrSheet > sheetNames.size())
     {
        for(int i = 1; i <= nCurrSheet; i++)
        {
            pExcel->addSheet();
        }
     }

     pWorksheet = dynamic_cast<QXlsx::Worksheet *>(pExcel->sheet(sheetNames.at(nCurrSheet -1)));
     if(pWorksheet)
     {
          bIsValid = true;
     }
     else
     {
          bIsValid = false;
          bIsOpen  = false;
          return bIsOpen;
     }
     //至此已打开，开始获取相应属性
     QXlsx::CellRange usedrange = pWorksheet->dimension();
     //因为excel可以从任意行列填数据而不一定是从0,0开始，因此要获取首行列下标
     nStartRow    = usedrange.firstRow();    //第一行的起始位置
     nStartColumn = usedrange.firstColumn();//第一列的起始位置
     nRowCount    = usedrange.rowCount();       //获取行数
     nColumnCount = usedrange.columnCount();    //获取列数
     bIsOpen  = true;

     return bIsOpen;
}

/**
  *@brief Open()的重载函数
  */
bool ExcelEngine::Open(QString xlsFile, uint nSheet, bool visible)
{
    sXlsFile   = xlsFile;
    nCurrSheet = nSheet;
    return Open(nCurrSheet);
}

bool ExcelEngine::OpenSheet(QString xlsFile, QString sheetName, QIODevice::OpenModeFlag flags)
{
    if(bIsOpen)
    {
        Close();
    }
    sXlsFile = xlsFile;
    if(NULL == pExcel)
    {
         pExcel = new QXlsx::Document(sXlsFile);
         if(pExcel)
         {
              bIsValid = true;
         }
         else
         {
              bIsValid = false;
              bIsOpen  = false;
              return bIsOpen;
         }
    }
    if(!bIsValid)
    {
         bIsOpen = false;
         return bIsOpen;
    }
    if(sXlsFile.isEmpty())
    {
         bIsOpen = false;
         return bIsOpen;
    }
    /*如果指向的文件不存在，则需要新建一个*/
    QFile f(sXlsFile);
    if(!f.exists())
    {
         bIsANewFile = true;
    } else
    {
         bIsANewFile = false;
    }
    if(!bIsANewFile)
    {

    }
    else
    {
        pExcel->addSheet(sheetName); //新建一个xls
    }
    QStringList sheetNames = pExcel->sheetNames();
    if(!sheetNames.contains(sheetName))
    {
        pExcel->addSheet(sheetName); //新建一个xls
    }
    else
    {
        if (flags == QIODevice::Truncate)
        {
            pExcel->deleteSheet(sheetName);
            pExcel->addSheet(sheetName); //新建一个xls
        }
    }
    pWorksheet = dynamic_cast<QXlsx::Worksheet *>(pExcel->sheet(sheetName));
    if(pWorksheet)
    {
        bIsValid = true;
    }
    else
    {
        bIsValid = false;
        bIsOpen  = false;
        return bIsOpen;
    }
    //至此已打开，开始获取相应属性
    QXlsx::CellRange usedrange = pWorksheet->dimension();
    //因为excel可以从任意行列填数据而不一定是从0,0开始，因此要获取首行列下标
    nStartRow    = usedrange.firstRow();    //第一行的起始位置
    nStartColumn = usedrange.firstColumn();//第一列的起始位置
    nRowCount    = usedrange.rowCount();       //获取行数
    nColumnCount = usedrange.columnCount();    //获取列数
    bIsOpen  = true;

    return bIsOpen;
}

void ExcelEngine::SetSheetHead(QList<QString> heads)
{
     for(int i=0;i<heads.size();i++)
     {
          this->SetCellData(1,i+1, heads.at(i));
     }
     nColumnCount = heads.size();
}

void ExcelEngine::SetSheetHead(QList<QString> heads, QString sheetName)
{
     for(int i=0;i<heads.size();i++)
     {
          this->SetCellData(1,i+1, heads.at(i), sheetName);
     }
}
/**
  *@brief 保存表格数据，把数据写入文件
  */
bool ExcelEngine::Save()
{
     if(pExcel)
     {
          //判断是否为只读
          QFileInfo info(sXlsFile);
          info.setFile(info.dir().path()+"/~$"+info.fileName());
          if(info.exists())          //判断一下,有没有"~$XXX.xlsx"文件存在,是不是为只读
          {
               QMessageBox::warning(NULL, tr("警告"), tr("文件保存失败"), QMessageBox::Yes | QMessageBox::No, QMessageBox::Yes);

               bIsSaveAlready = true;
               return false;
          }

          if (pExcel->saveAs(sXlsFile))
          {
               bIsSaveAlready = true;
          }
     }
     return false;
}

void ExcelEngine::Close()
{
     if(pExcel && pWorksheet)
     {
          pExcel = NULL;
          bIsOpen        = false;
          bIsValid       = false;
          bIsANewFile    = false;
          bIsSaveAlready = true;
     }

}

bool ExcelEngine::SaveDataFrTable(QTableWidget *tableWidget)
{
     if(NULL == tableWidget)
     {
          return false;
     }
     if(!bIsOpen)
     {
          return false;
     }
     //先请空工作簿
     this->clearSheet();

     int tableR = tableWidget->rowCount();
     int tableC = tableWidget->columnCount();
     QList<QList<QVariant>> dataList;
     QVariantList vars;


     //先将tablewidget的数据保存到二维数组里再一次性写入文件
     for (int row = 0; row < tableR; row++)
     {
          QVariantList varRow;
          for (int col = 0; col <tableC; col++)
          {
               if (tableWidget->item(row,col) != NULL)
               {
                    varRow.append(tableWidget->item(row, col)->text());
               }
               else
                    varRow.append(QVariant());
          }
          dataList.append(varRow);
     }

     for(int i = 1; i <= tableC ; i++)
     {
          pWorksheet->write(1, i, tableWidget->horizontalHeaderItem(i - 1)->data(0).toString());
     }

     for (int row = 2; row <= tableR +1; row ++)
     {
          for (int col = 1 ; col <= tableC; col ++)
          {

               pWorksheet->write(row, col, dataList.at(row -2 ).at(col -1));
          }
     }
     Save();
     return true;
}

bool ExcelEngine::ReadDataToTable(QTableWidget *tableWidget)
{
     if(NULL == tableWidget)
     {
          return false;
     }
     //先把table的内容清空
     tableWidget->clear();
     tableWidget->setColumnCount(0);
     QList<QList<QVariant>> datas = getAllData();
     //获取excel中的第一行数据作为表头
     QStringList headerList;
     for(int n = 0;n<datas.at(0).size();n++)
     {
          headerList<<datas.at(0).at(n).toString();
     }

     //重新创建表头
     tableWidget->setColumnCount(nColumnCount);
     tableWidget->setHorizontalHeaderLabels(headerList);

     //插入新数据
     //插入新数据
     for(int i= 1,r=0; i<datas.size() -1;i++,r++)//行
     {
          tableWidget->insertRow(r); //插入新行
          for(int j= 0, c=0; j < datas.at(i).size();  j++, c++)//列
          {
               tableWidget->setItem(r,c,new QTableWidgetItem(datas.at(i).at(j).toString()));
          }
     }
     return true;

}

QList<QList<QVariant> > ExcelEngine::getAllData()
{
    QList<QList<QVariant>> datas;
    if (pWorksheet == NULL)
         return datas;
    QXlsx::CellRange usedrange = pWorksheet->dimension();
    for (int row = usedrange.firstRow(); row <= usedrange.rowCount(); row ++)
    {
         QVariantList varRow;
         for (int col = usedrange.firstColumn(); col <= usedrange.columnCount(); col ++)
         {
              varRow.append(pWorksheet->read(row, col));
         }
         datas.append(varRow);
    }
    return  datas;
}

bool ExcelEngine::setAllData(QList<QList<QVariant> > data, bool isEnable, int row, int col)
{
     int rowStart = 0;
     int colStart = 0;

     if (!isEnable)
     {
          rowStart = nStartRow +1; // 留出一行给表头
          colStart = nStartColumn;
     }
     else
     {
          rowStart = row; //设定了从哪行开始就从哪行开始
          colStart = col;
     }
     //        int maxCount = data.at(0).size();
     //        foreach(auto it, data)
     //        {
     //            if(maxCount < it.size())
     //                maxCount = it.size();
     //        }
     for (int row = 0 ; row < data.size() ; row ++)
     {
          for (int col = 0 ; col < data.at(row).size() ; col ++)
          {
               pWorksheet->write(row + 2, col + 1 , data.at(row).at(col));
          }
     }
}
/**
  *@brief 获取指定单元格的数据
  *@param row : 单元格的行号
  *@param column : 单元格的列号
  *@return [row,column]单元格对应的数据
  */
QVariant ExcelEngine::GetCellData(uint row, uint column)
{
     QVariant data;
    if (pWorksheet == NULL)
            return data;
     data = pWorksheet->read(row, column);
     return data;
}

bool ExcelEngine::SetCellData(uint row, uint column, QVariant data)
{
    if (pWorksheet == NULL)
         return false;

    return  pWorksheet->write(row, column, data);;
}
/**
  *@brief 修改指定单元格的数据
  *@param row : 单元格的行号
  *@param column : 单元格指定的列号
  *@param data : 单元格要修改为的新数据
  *@return 修改是否成功 true : 成功
  *                   false: 失败
  */
bool ExcelEngine::SetCellData(uint row, uint column, QVariant data, QString sheetName)
{
  QXlsx::Worksheet * psheet = dynamic_cast<QXlsx::Worksheet *>(pExcel->sheet(sheetName));
  if (psheet == NULL)
       return false;

  return  psheet->write(row, column, data);;
}

bool ExcelEngine::clearSheet()
{
    if (pWorksheet == NULL)
            return false;
     int rowcnt    = pWorksheet->dimension().rowCount();
     int columncnt = pWorksheet->dimension().columnCount();

     for(int i= pWorksheet->dimension().firstRow() ; i<rowcnt; i++)//行
     {
          for(int j= pWorksheet->dimension().firstColumn(); j<columncnt; j++)//列
          {
               if (! pWorksheet->write(i, j, QVariant()))
                    return  false;
          }
     }
     return true;
}
/**
  *@brief 清空除报表之外的数据
  */
void ExcelEngine::Clear()
{
     sXlsFile     = "";
     nRowCount    = 0;
     nColumnCount = 0;
     nStartRow    = 0;
     nStartColumn = 0;
}
/**
  *@brief 判断excel是否已被打开
  *@return true : 已打开
  *        false: 未打开
  */
bool ExcelEngine::IsOpen()
{
     return bIsOpen;
}
/**
  *@brief 判断excel COM对象是否调用成功，excel是否可用
  *@return true : 可用
  *        false: 不可用
  */
bool ExcelEngine::IsValid()
{
     return bIsValid;
}

bool ExcelEngine::IsANewFile()
{
     return bIsANewFile;
}

/**
  *@brief 获取excel的行数
  */
uint ExcelEngine::GetRowCount()const
{
    if (pWorksheet == NULL)
            return 0;

    return pWorksheet->dimension().rowCount();
}

uint ExcelEngine::GetRowCount(QString sheetName) const
{
 QXlsx::Worksheet * psheet = dynamic_cast<QXlsx::Worksheet *>(pExcel->sheet(sheetName));
 if (psheet == NULL)
         return 0;

 return psheet->dimension().rowCount();
}
/**
  *@brief 获取excel的列数
  */
uint ExcelEngine::GetColumnCount()const
{
    if (pWorksheet == NULL)
            return 0;
    return pWorksheet->dimension().columnCount();
}

uint ExcelEngine::GetColumnCount(QString sheetName) const
{
 QXlsx::Worksheet * psheet = dynamic_cast<QXlsx::Worksheet *>(pExcel->sheet(sheetName));
    if (psheet == NULL)
            return 0;
    return psheet->dimension().columnCount();

}
